﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using OnlineAuction.Common;
using OnlineAuction.Entity;

namespace OnlineAuction.DataAccess
{
    public class AuctionDA
    {
        public string GetConnection() {
            Database db = new Database();
            return db.ConnectString;
        }

        public DataSet ViewAuction()
        {
            SqlConnection conn;
            Database db = new Database();
            using (conn = db.GetConnection())
            {
                try
                {
                    conn.Open();
                    DataSet ds = new DataSet();
                    SqlDataAdapter da = new SqlDataAdapter("ViewAuction", conn);
                    da.SelectCommand.CommandType = CommandType.StoredProcedure;
                    da.Fill(ds, "Auction");
                    conn.Close();
                    return ds;
                }
                catch
                {
                    conn.Close();
                    return null;
                }
                finally
                {
                    conn.Dispose();
                }
            }
        }

        public DataSet ViewDetailItem(int AuctionID)
        {
            SqlConnection conn;
            Database db = new Database();
            //Get connection to database
            using (conn = db.GetConnection())
            {
                //Get detail of Item and auction session
                try
                {
                    conn.Open();
                    DataSet ds = new DataSet();
                    SqlDataAdapter da = new SqlDataAdapter("ViewDetailItem", conn);
                    da.SelectCommand.CommandType = CommandType.StoredProcedure;
                    da.SelectCommand.Parameters.AddWithValue("@AuctionID", AuctionID);
                    da.Fill(ds, "Auction");
                    conn.Close();
                    return ds;
                }
                catch
                {
                    conn.Close();
                    return null;
                }
                finally
                {
                    conn.Dispose();
                }
            }
        }

        public bool FinishAuctionSessionImmediately(int auctionID)
        {
            SqlConnection conn;
            Database db = new Database();
            //Get connection to database
            using (conn = db.GetConnection())
            {
                //set finish auction
                try
                {
                    conn.Open();
                    SqlCommand cmd = new SqlCommand("FinishAuctionSessionImmediately", conn);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@AuctionID", auctionID);
                    cmd.ExecuteNonQuery();
                    conn.Close();
                    return true;
                }
                catch
                {
                    conn.Close();
                    return false;
                }
                finally
                {
                    conn.Dispose();
                }
            }
        }

        public DataSet Statistic(int CategoryID, string TimeFrom, string TimeTo,
                double PriceFrom, double PriceTo, int AuctionStateID)
        {
            SqlConnection conn;
            Database db = new Database();
            //Get connection to database
            using (conn = db.GetConnection())
            {
                //Get detail of Item and auction session
                try
                {
                    conn.Open();
                    DataSet ds = new DataSet();
                    SqlDataAdapter da = new SqlDataAdapter("Statistic", conn);
                    da.SelectCommand.CommandType = CommandType.StoredProcedure;
                    da.SelectCommand.Parameters.AddWithValue("@CategoryID", CategoryID);
                    da.SelectCommand.Parameters.AddWithValue("@TimeFrom", TimeFrom);
                    da.SelectCommand.Parameters.AddWithValue("@TimeTo", TimeTo);
                    da.SelectCommand.Parameters.AddWithValue("@PriceFrom", PriceFrom);
                    da.SelectCommand.Parameters.AddWithValue("@PriceTo", PriceTo);
                    da.SelectCommand.Parameters.AddWithValue("@AuctionStateID", AuctionStateID);
                    da.Fill(ds);
                    conn.Close();
                    return ds;
                }
                catch
                {
                    conn.Close();
                    return null;
                }
                finally
                {
                    conn.Dispose();
                }
            }
        }

        public DataSet GetOnlineAuctions(string search)
        {
            SqlConnection conn;
            Database db = new Database();
            DataSet ds = new DataSet();
            SqlDataAdapter da = new SqlDataAdapter();
            using (conn = db.GetConnection())
            {
                try
                {
                    conn.Open();
                    ds = new DataSet();
                    da = new SqlDataAdapter("GetAuctionAndItem", conn);
                    da.SelectCommand.CommandType = CommandType.StoredProcedure;
                    da.SelectCommand.Parameters.Add(new SqlParameter("@search", search));
                    da.Fill(ds, "Auction");
                    conn.Close();
                    return ds;
                }
                catch
                {
                    conn.Close();
                    return null;
                }
                finally
                {
                    conn.Dispose();
                }
            }
        }

        public bool BlockItemAndAuction(int AuctionID)
        {
            //Get connection to database
            SqlConnection conn;
            Database db = new Database();
            using (conn = db.GetConnection())
            {
                //Block Item And Auction
                try
                {
                    conn.Open();
                    SqlCommand command = new SqlCommand("BLockItemAndAuction", conn);
                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.Add(new SqlParameter("@AuctionID", AuctionID));
                    command.ExecuteNonQuery();
                    conn.Close();
                    return true;
                }
                catch
                {
                    conn.Close();
                    return false;
                }
                finally
                {
                    conn.Dispose();
                }
            }
        }

        public List<string> GetYear()
        {

            SqlConnection conn;
            Database db = new Database();
            using (conn = db.GetConnection())
            {
                try
                {
                    conn.Open();

                    SqlCommand cmd = new SqlCommand("GetYear", conn);
                    cmd.CommandType = CommandType.StoredProcedure;
                    SqlDataReader dr = cmd.ExecuteReader();
                    List<string> list = new List<string>();
                    while (dr.Read())
                    {
                        list.Add(dr["Year"].ToString());
                    }
                    conn.Close();
                    return list;
                }
                catch
                {
                    conn.Close();
                    return null;
                }
                finally
                {
                    conn.Dispose();
                }
            }
        }

        public List<string> GetMonth(string Year)
        {

            SqlConnection conn;
            Database db = new Database();
            using (conn = db.GetConnection())
            {
                try
                {
                    conn.Open();

                    SqlCommand cmd = new SqlCommand("GetMonth", conn);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@Year", Year);
                    SqlDataReader dr = cmd.ExecuteReader();
                    List<string> list = new List<string>();
                    while (dr.Read())
                    {
                        list.Add(dr["Month"].ToString());
                    }
                    conn.Close();
                    return list;
                }
                catch
                {
                    conn.Close();
                    return null;
                }
                finally
                {
                    conn.Dispose();
                }
            }
        }

        public List<string> GetDay(string Year, string Month)
        {

            SqlConnection conn;
            Database db = new Database();
            using (conn = db.GetConnection())
            {
                try
                {
                    conn.Open();

                    SqlCommand cmd = new SqlCommand("GetDay", conn);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@Year", Year);
                    cmd.Parameters.AddWithValue("@Month", Month);
                    SqlDataReader dr = cmd.ExecuteReader();
                    List<string> list = new List<string>();
                    while (dr.Read())
                    {
                        list.Add(dr["Day"].ToString());
                    }
                    conn.Close();
                    return list;
                }
                catch
                {
                    conn.Close();
                    return null;
                }
                finally
                {
                    conn.Dispose();
                }
            }
        }

        public List<AuctionDTO> Revenue(string Year, string Month, string Day)
        {
            SqlConnection conn;
            Database db = new Database();
            using (conn = db.GetConnection())
            {
                try
                {
                    conn.Open();

                    SqlCommand cmd = new SqlCommand("Revenue", conn);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@Year", Year);
                    cmd.Parameters.AddWithValue("@Month", Month);
                    cmd.Parameters.AddWithValue("@Day", Day);
                    SqlDataReader dr = cmd.ExecuteReader();
                    List<AuctionDTO> list = new List<AuctionDTO>();
                    while (dr.Read())
                    {
                        list.Add(new AuctionDTO(int.Parse(dr["AuctionID"].ToString()), dr["Seller"].ToString(),
                            dr["TimeStartAuction"].ToString(), dr["TimeFinishAuction"].ToString(),
                            double.Parse(dr["FirstPrice"].ToString()),
                            double.Parse(dr["FinalPrice"].ToString()), dr["Buyer"].ToString(),
                            double.Parse(dr["FeeMoney"].ToString())));
                    }
                    conn.Close();
                    return list;
                }
                catch
                {
                    conn.Close();
                    return null;
                }
                finally
                {
                    conn.Dispose();
                }
            }
        }
    }
}
